# encoding:utf-8
# user: ares at 18-9-18

import pymysql
from ershoufang import settings

MYSQL_HOST=settings.MYSQL_HOSTS
MYSQL_USER=settings.MYSQL_USER
MYSQL_PASSWORD=settings.MYSQL_PASSWORD
MYSQL_PORT=settings.MYSQL_PORT
MYSQL_DB=settings.MYSQL_DB

conn=pymysql.connect(host=MYSQL_HOST,port=MYSQL_PORT,user=MYSQL_USER,passwd=MYSQL_PASSWORD,db=MYSQL_DB)
cur=conn.cursor()

class Sql():
    @classmethod
    def select_title(cls,title,laiyuan):
        sql="select exists(select 1 from ershoufang where title=%(title)s and laiyuan=%(laiyuan)s)"
        value={
            'title':title,
            'laiyuan':laiyuan
        }
        cur.execute(sql,value)
        return cur.fetchall()[0]

    #title | huxing | mianji | chaoxiang | year | quyu | jiedao | xiaoqu | address | jtms | zongje | danjia | louceng | zhuangxiu | zhuzhaileixing | cqxz | laiyuan |
    @classmethod
    def insert_ershoufang(cls,dictInfo):
        dictInfo=dict(dictInfo)
        sql="insert into ershoufang(title,huxing,mianji,chaoxiang,year,quyu," \
            "jiedao,xiaoqu,address,jtms,zongje,danjia,louceng,zhuangxiu," \
            "zhuzhaileixing,cqxz,laiyuan) " \
            " values (%(title)s, %(huxing)s, %(mianji)s, %(chaoxiang)s, %(year)s, " \
            "%(quyu)s, %(jiedao)s, %(xiaoqu)s, %(address)s, %(jtms)s, %(zongje)s, %(danjia)s, " \
            "%(louceng)s, %(zhuangxiu)s, %(zhuzhaileixing)s, %(cqxz)s, %(laiyuan)s)"


        #print(sql)
        value={
            'title':dictInfo['title'],
            'huxing':dictInfo['huxing'],
            'mianji':dictInfo['mianji'],
            'chaoxiang':dictInfo['chaoxiang'],
            'year':dictInfo['year'],
            'quyu':dictInfo['quyu'],
            'jiedao':dictInfo['jiedao'],
            'xiaoqu':dictInfo['xiaoqu'],
            'address':dictInfo['address'],
            'jtms':dictInfo['jtms'],
            'zongje':dictInfo['zongje'],
            'danjia':dictInfo['danjia'],
            'louceng':dictInfo['louceng'],
            'zhuangxiu':dictInfo['zhuangxiu'],
            'zhuzhaileixing':dictInfo['zhuzhaileixing'],
            'cqxz':dictInfo['cqxz'],
            'laiyuan':dictInfo['laiyuan']
        }
        #try:
        cur.execute(sql,value)

        #except:
        #   conn.rollback()
        #finally:
        cur.close()
        conn.commit()
        conn.close()
        #print(dictInfo['laiyuan'])

